package com.zsrt.oa.user.dao.impl;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import com.zsrt.oa.user.dao.IUserDao;
import com.zsrt.oa.user.entity.UserEntity;
import com.zsrt.oa.user.utils.DBUtil;

public class UserDaoImpl implements IUserDao {

	@Override
	public void add(UserEntity entity){
		
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1.加载驱动：连接mysql和Oracle的驱动不一样
			Class.forName("com.mysql.jdbc.Driver");
			
			//2.使用用户名root 和 密码 aaa123 连接数据库
			String url = "jdbc:mysql://127.0.0.1:3306/scott_wrl";
			String user = "root";
			String password = "aaa123";
			conn = DriverManager.getConnection(url, user, password);
			
			//3.创建sql语句
			/*String sql = "INSERT INTO oa_user_t VALUES("+entity.getId()+",'"+entity.getName()+"','"
					+entity.getPhone()+"','"+entity.getPassword()+"','"
					+entity.getBirthday()+"','"+entity.getPunchTime()+"')";*/
			String sql = "INSERT INTO oa_user_t VALUES(?,?,?,?,?,?);";
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1,entity.getId());
			ps.setString(2, entity.getName());
			ps.setString(3, entity.getPhone());
			ps.setString(4, entity.getPassword());
			ps.setString(5, entity.getBirthday());
			ps.setString(6, entity.getPunchTime());
			//4.执行sql语句，修改数据库
			ps.executeUpdate();
			System.out.println("插入记录成功！");
		} catch (ClassNotFoundException e) {
			System.err.println("数据库连接异常...");
			e.printStackTrace();
		} catch (SQLException e) {
			System.err.println("sql语句执行异常...");
			e.printStackTrace();
		}finally {
			//关闭连接
			if(null != ps){
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(null != conn){
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	@Override
	public void del(UserEntity entity) throws Exception {
		//1.加载驱动
		//2.新建连接
		Connection conn = DBUtil.getConnection();
		//3.创建sql语句
		String sql = "DELETE FROM oa_user_t WHERE id = ?;";
		//4.执行sql语句
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, entity.getId());
		ps.executeUpdate();
		
		//5.关闭连接
		ps.close();
		conn.close();
		System.out.println("删除成功");
	}

	@Override
	public int update(UserEntity search,UserEntity entity) throws Exception {
		//1.加载驱动：连接mysql和Oracle的驱动不一样
		//2.连接数据库
		Connection connection = DBUtil.getConnection();
				
		//3.创建sql语句
		String sql = "UPDATE oa_user_t set name = ?,password = ?,phone = ?,birthday = ?,punch_time = ?  WHERE id = ?;";
		//4.执行sql语句
		PreparedStatement ps = connection.prepareStatement(sql);
		
		/**
		 * --每对一个新的表（字段不一样的表）操作时，都要重新写一些ps.setXxx()
		 * 	》重要的：可以通过获取到属性-->（转换大写）对应的表格字段
		 *		-->再通过equalsIgnoreCase 的reg（“get/set属性”），这样就可以准确的对表格进行操作
		 */
		
		//---------获取get/set所有方法---------
//		Class<?> clazz = Class.forName("com.zsrt.oa.user.entity.UserEntity");
        Method[] methods = entity.getClass().getDeclaredMethods();			//相当于中介
        int temp = 1;		//计算器
        for(int index = 0 ; index < methods.length; index++){
        	String methodName = methods[index].getName();
        	//1)获取所有的get方法
        	String reg = "get.*";
        	if(!methodName.equalsIgnoreCase("getid") && methodName.matches(reg)){
        		//2)执行方法
//        		System.out.println(methods[index].invoke(entity));					
        		ps.setString(temp++, (String) methods[index].invoke(entity));	//ps相当于用人单位：
        		//根据传进来的entity（做多种字段类型的判断）使用这种判断，现在就不用每传一个新的entity都ps.setXx()
        	}
        }
		//------------------
		
		ps.setInt(temp, search.getId());
		int counter = ps.executeUpdate();
		
		//5.关闭连接
		ps.close();
		connection.close();
		return counter;
	}

	@Override
	public UserEntity query(Integer id) throws Exception {
		UserEntity entity = new UserEntity();
		//1.加载驱动
		//2.新建连接
		Connection conn = DBUtil.getConnection();
		//3.创建sql语句
		String sql = "SELECT ID AS id,NAME AS name,PASSWORD AS password,"
				+ "PHONE AS phone,BIRTHDAY AS birthday,PUNCH_TIME AS punchTime"
				+ " FROM oa_user_t  WHERE id = ?;";
		
		//4.执行sql语句
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		
		//5.使用结果集，取到数据并保存为UserEntity
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData metaData = rs.getMetaData();		//获取字段名
		
		
		//----a.获取java对象的所有自定义方法
		Method[] methods = entity.getClass().getDeclaredMethods();
		
		while(rs.next()){
			int index = 1;
			while(index <= metaData.getColumnCount()){
//				System.out.print(metaData.getColumnName(index)+":"+rs.getString(index++)+"\t");
				
				//请注意 methods跟 数据库所有字段  是多对多的关系
				for(Method method:methods){
					String methodName = method.getName();
					//---b.确定哪个set方法，设置哪个属性（在所有set方法中找到对应属性的set方法）
					if(methodName.equalsIgnoreCase("set"+removeBottom(metaData.getColumnName(index)))){
						//"set"+removeBottom(..)是固定的，当匹配了这个字段后，跳到下一个字段（index++）
						
//						Class<?>[]pp = method.getParameterTypes();
						Class<?> forName = Class.forName(method.getParameterTypes()[0].getName());
						
						//---c.判断根据》对象属性的类型《，执行set方法（注意 参数类型）
						if(Integer.class == forName){					//只做了两个判断，做多个类型的判断可以使用switch
							method.invoke(entity, Integer.parseInt(rs.getString(index++)));
						}else{
							method.invoke(entity, rs.getString(index++));	//调用 set 方法将传入的value值保存属性中
						}
						break;
					}
				}
			}
		}
		//5.关闭资源
		DBUtil.closeResource(rs, ps, conn);
		return entity;
	}
	
	//去掉下划线
	public static String removeBottom(String name){
		String[] strArray = name.split("_");
		String newName = "";
		for(String str:strArray){
//			System.out.println(str+" ");
			newName += str;
		}
		return newName;
	}
}
